package top.zhangjianyong.excel20241029;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import top.zhangjianyong.utils.JDBCUtil;
import top.zhangjianyong.utils.file.ExcelUtil;

import java.io.IOException;
import java.sql.SQLException;

/**
 * @author zhangjianyong
 * @since 2024/10/29 上午9:57
 */
@Slf4j
public class Test {

    static {
        String host = "mysql.zhangjianyong.top";
        String port = "3306";
        String dbname = "test";
        String url = "jdbc:mysql://" + host + ":" + port + "/" + dbname + "?useSSL=false&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Hongkong";
        String user = "zjy_test";
        String password = "ZhangJianYong@Test2024";

        JDBCUtil.init(url, user, password);

    }

    private final static String excel1 = "/Users/zhangjianyong/Desktop/excel/合并户籍.xlsx";

    public static void main(String[] args) throws IOException, SQLException {

        //读取数据
        XSSFWorkbook sheets = new XSSFWorkbook(excel1);
        //江边非农户口
        XSSFSheet sheet1 = sheets.getSheet("江边非农户口");
        int lastRowNum = sheet1.getLastRowNum();
        for (int i = 1; i <= lastRowNum; i++) {
            XSSFRow row = sheet1.getRow(i);
            String name = getValue(row, 1);
            String idNo = getValue(row, 5);
            String address = getValue(row, 8);
            String phone = getValue(row, 11);
            String key = "江边非农户口-" + (i + 1);
            log.info("{}-{}-{}-{}-{}", name, idNo, address, phone, key);
            insertSql(name, idNo, phone, address, key);
        }

    }

    private static void insertSql(String name, String idNo,
                                  String phone, String address,
                                  String key) throws SQLException {
        JDBCUtil.insert("insert into people(name,id_no,phone,address,excel_key) VALUES(?,?,?,?,?)",
                name, idNo, phone, address, key);
    }

    private static String getValue(XSSFRow row, int num) {
        XSSFCell cell = row.getCell(num);
        CellType cellTypeEnum = cell.getCellTypeEnum();
        switch (cellTypeEnum) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case ERROR:
                return String.valueOf(cell.getErrorCellValue());
            case FORMULA:
                return String.valueOf(cell.getCellFormula());
            case BLANK:
            default:
                return null;
        }
    }
}
